package com.gxuwz.attend.dao;

import com.gxuwz.attend.entity.Department;
import com.gxuwz.attend.entity.Instructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//辅导员Dao
public class InstructorDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加辅导员
     * @param inst
     */
    public void insertInst(Instructor inst){
        String sql ="insert into sys_instructor(instID,instName,depID,telephone)values(?,?,?,?)";
        jdbcTemplate.update(sql,inst.getInstID(),inst.getInstName(),inst.getDepID(),inst.getTelephone());
    }

    /**
     * 删除辅导员
     * @param instID
     */
    public void deleteInst(String instID){
        String sql = "delete from sys_instructor where instID=?";
        jdbcTemplate.update(sql,instID);
    }

    /**
     * 更新辅导员信息
     * @param inst
     */
    public void updateInst(Instructor inst){
        String sql = "update sys_instructor set instName=?,depID=?,telephone=? where instID=?";
        jdbcTemplate.update(sql,inst.getInstName(),inst.getDepID(),inst.getTelephone(),inst.getInstID());
    }
    /**
     * 查询全部的辅导员数据
     * @return
     */
    public List<Instructor> queryInsts(){
        String sql = "select * from sys_instructor";
        List<Object> paramValues = new ArrayList<>();
        List<Instructor> instList = new ArrayList<>();
        instList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Instructor>() {
            @Override
            public Instructor mapRow(ResultSet resultSet, int i) throws SQLException {
                Instructor inst = new Instructor();
                String instID = resultSet.getString("instID");
                String instName = resultSet.getString("instName");
                String depID = resultSet.getString("depID");
                String telephone = resultSet.getString("telephone");
                inst.setInstID(instID);
                inst.setInstName(instName);
                inst.setDepID(depID);
                inst.setTelephone(telephone);
                return inst;
            }
        });
        return instList;
    }
    /**
     * 模糊查询辅导员数据
     * @param inst
     * @return
     */
    public List<Instructor> queryInst(Instructor inst){
        String sql = "select * from sys_instructor where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Instructor> instList = new ArrayList<>();
        if(inst!=null){
            if(inst.getInstID()!=null && !"".equals(inst.getInstID().trim())){
                sql += " and instID=?";
                paramValues.add(inst.getInstID());
            }
            if(inst.getDepID()!=null && !"".equals(inst.getDepID().trim())){
                sql += " and depID=?";
                paramValues.add(inst.getDepID());
            }
            if (inst.getTelephone()!=null && !"".equals(inst.getTelephone().trim())){
                sql += "and telephone=?";
                paramValues.add(inst.getTelephone());
            }
            if(inst.getInstName()!=null && !"".equals(inst.getInstName().trim())){
                sql += " and instName like ?";
                paramValues.add(inst.getInstName()+"%");
            }
        }
        instList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Instructor>() {
            @Override
            public Instructor mapRow(ResultSet resultSet, int i) throws SQLException {
                Instructor inst = new Instructor();
                String instID = resultSet.getString("instID");
                String instName = resultSet.getString("instName");
                String depID = resultSet.getString("depID");
                String telephone = resultSet.getString("telephone");
                inst.setInstID(instID);
                inst.setInstName(instName);
                inst.setDepID(depID);
                inst.setTelephone(telephone);
                return inst;
            }
        });
        return instList;
    }
}
